using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using XPStudio.DBTools.Sql;

namespace WHCDC.AIDSQuery
{
    public class  UnitDAL
    {
        public  UnitDAL()
        { }
        #region FIELD & PARAM & SP
        private const string FIELD_UNITID = "Unitid";
        private const string FIELD_UNITNAME = "UnitName";
        private const string FIELD_UNITDISTRICT = "UnitDistrict";
        private const string FIELD_UNITLEVEL = "UnitLevel";
        private const string FIELD_UNITUSERID = "UnitUserid";
        private const string FIELD_UNITORDERID = "UnitOrderID";
        private const string FIELD_USERID = "userid";

        private const string PARAM_UNITID = "@Unitid";
        private const string PARAM_UNITNAME = "@UnitName";
        private const string PARAM_UNITDISTRICT = "@UnitDistrict";
        private const string PARAM_UNITLEVEL = "@UnitLevel";
        private const string PARAM_UNITUSERID = "@UnitUserid";
        private const string PARAM_UNITORDERID = "@UnitOrderID";
        private const string PARAM_USERID = "@userid";

        private const string SP_UNITADD = "AIDS_SampleUnitAdd";
        private const string SP_UNITDEL = "AIDS_SampleUnitDel";
        private const string SP_UNITUPDATE = "AIDS_SampleUnitUpdate";
        private const string SP_GETUNIT = "AIDS_GetSampleUnitByID";
        private const string SP_GETUNIT2 = "AIDS_GetSampleUnitByName";
        private const string SP_GETUNITLIST = "AIDS_SampleUnitList";
        private const string SP_ADDUSERTOUNIT = "AIDS_AddUserToUnit";
        private const string SP_DELUSERFROMUNIT = "AIDS_DelUserFromUnit";
        #endregion

        public static int AddUnit(UnitModel unit)
        {
            Database2 db = new Database2();
            SqlParameter[] parms={
                db.MakeInParam(PARAM_UNITNAME, SqlDbType.NVarChar,200,unit.UnitName),
                db.MakeInParam(PARAM_UNITDISTRICT, SqlDbType.NVarChar, 100,unit.UnitDistrict),
                db.MakeInParam(PARAM_UNITLEVEL , SqlDbType.Int,4,unit.UnitLevel),
                db.MakeInParam(PARAM_UNITUSERID, SqlDbType.Int,4,unit.UnitUserid)};
            int unitid = db.RunProc(SP_UNITADD,parms);
            return unitid;

        }

        public static bool DelUnit(int unitid)
        {
            Database2 db = new Database2();
            SqlParameter[] parms={
                db.MakeInParam(PARAM_UNITID, SqlDbType.Int,4,unitid)};
            if ( db.RunProc(SP_UNITDEL,parms) == 0)
                return true;
            else
                return false;
        }

        public static bool UpdateUnit(UnitModel unit)
        {
            Database2 db = new Database2();
            SqlParameter[] parms ={
                db.MakeInParam(PARAM_UNITID, SqlDbType.Int,4,unit.UnitID),
                db.MakeInParam(PARAM_UNITNAME, SqlDbType.NVarChar,200,unit.UnitName),
                db.MakeInParam(PARAM_UNITDISTRICT, SqlDbType.NVarChar,100,unit.UnitDistrict),
                db.MakeInParam(PARAM_UNITLEVEL, SqlDbType.Int,4,unit.UnitLevel),
                db.MakeInParam(PARAM_UNITUSERID, SqlDbType.Int,4,unit.UnitUserid),
                db.MakeInParam(PARAM_UNITORDERID , SqlDbType.Int,4,unit.UnitOrderId)
            };
            if (db.RunProc(SP_UNITUPDATE, parms) == 0)
                return true;
            else
                return false;
        }

        public static DataSet GetUnitList()
        {
            Database2 db = new Database2();
            DataSet ds;
            db.RunProc(SP_GETUNITLIST, out ds, "unitlist");
            return ds;
        }

        public static DataSet GetUnitList(string unitdistrict)
        {
            Database2 db = new Database2();
            DataSet ds;
            SqlParameter[] parms ={
                db.MakeInParam(PARAM_UNITDISTRICT, SqlDbType.NVarChar,100,unitdistrict)
            };
            db.RunProc(SP_GETUNITLIST,parms, out ds, "unitlist");
            return ds;
        }

        public static UnitModel GetUnit(int unitid)
        {
            Database2 db = new Database2();
            UnitModel unit = new UnitModel();
            SqlParameter[] parms = {
                db.MakeInParam(PARAM_UNITID, SqlDbType.Int,4,unitid)};
            SqlDataReader dr;
            db.RunProc(SP_GETUNIT, parms, out dr);
            if (dr.Read())
            {
                unit.UnitID = (int)dr[FIELD_UNITID];
                unit.UnitName = (string)dr[FIELD_UNITNAME];
                unit.UnitDistrict = (string)dr[FIELD_UNITDISTRICT];
                unit.UnitLevel = (int)dr[FIELD_UNITLEVEL];
                unit.UnitUserid = dr[FIELD_UNITUSERID] == DBNull.Value ? 0 : (int)dr[FIELD_UNITUSERID];
                unit.UnitOrderId = dr[FIELD_UNITORDERID] == DBNull.Value ? 999 : (int)dr[FIELD_UNITORDERID];
                dr.Close();
            }
            else
                unit = null;
            return unit;
        }


        public static UnitModel GetUnit(string unitname)
        {
            Database2 db = new Database2();
            UnitModel unit = new UnitModel();
            SqlParameter[] parms = {
                db.MakeInParam(PARAM_UNITNAME, SqlDbType.NVarChar,200,unitname)};
            SqlDataReader dr;
            db.RunProc(SP_GETUNIT2, parms, out dr);
            if (dr.Read())
            {
                unit.UnitID = (int)dr[FIELD_UNITID];
                unit.UnitName = (string)dr[FIELD_UNITNAME];
                unit.UnitDistrict = (string)dr[FIELD_UNITDISTRICT];
                unit.UnitLevel = (int)dr[FIELD_UNITLEVEL];
                unit.UnitUserid = dr[FIELD_UNITUSERID] == DBNull.Value ? 0 : (int)dr[FIELD_UNITUSERID];
                unit.UnitOrderId = dr[FIELD_UNITORDERID] == DBNull.Value ? 999 : (int)dr[FIELD_UNITORDERID];
                dr.Close();
            }
            else
                unit = null;
            return unit;
        }

        public static bool AddUserToUnit(int unitid, int userid)
        {
            Database2 db = new Database2();
            SqlParameter[] parms ={
                db.MakeInParam(PARAM_UNITID, SqlDbType.Int, 4, unitid),
                db.MakeInParam(PARAM_USERID , SqlDbType.Int,4,userid)
            };
            if (db.RunProc(SP_ADDUSERTOUNIT, parms) == 0)
                return true;
            else
                return false;
        }

        public static bool DelUserFromUnit(int unitid, int userid)
        {
            Database2 db = new Database2();
            SqlParameter[] parms ={
                db.MakeInParam(PARAM_UNITID, SqlDbType.Int, 4, unitid),
                db.MakeInParam(PARAM_USERID , SqlDbType.Int,4,userid)
            };
            if (db.RunProc(SP_DELUSERFROMUNIT, parms) == 0)
                return true;
            else
                return false;
        }

        public static DataSet GetUnitUser(int unitid)
        {
            Database2 db = new Database2();
            
            string sqlcmd = "select su.unitid, uu.userid,uu.username from aids_sampleunituser su, ums_users uu ";
              sqlcmd +=" where su.userid = uu.userid and su.unitid="+unitid.ToString();
            DataSet ds;
            db.RunCommand(sqlcmd, out ds, "users");
            return ds;
        }
    }
}
